﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class DataBound : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            categoryDropList.DataTextField = "CategoryName"; //  -> Bên WinForm là DisplayMember
            categoryDropList.DataValueField = "CategoryID"; //  -> Bên WinForm là ValueMember
            categoryDropList.DataSource = GetCategories();
            categoryDropList.DataBind(); // -> Chỉ có bên web, WinForm tự động Bind dữ liệu
        }
    }

    private DataTable GetCategories()
    {
        // Load dữ liệu cho combobox, DB Northwin, Table Categories
        // dùng DataTable
        DataTable categoryTable = new DataTable();

        using (SqlConnection sqlConnection = new SqlConnection())
        {
            sqlConnection.ConnectionString =
                "Data Source=dell-n5110;Initial Catalog=Northwind; Integrated Security=true;";

            sqlConnection.Open();

            SqlCommand selectCommand = new SqlCommand("SELECT * FROM Categories", sqlConnection);
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(selectCommand);

            sqlDataAdapter.Fill(categoryTable);
        }

        return categoryTable;
    }

    private DataTable GetProducts()
    {
        // Load dữ liệu cho combobox, DB Northwin, Table Categories
        // dùng DataTable
        DataTable productTable = new DataTable();

        using (SqlConnection sqlConnection = new SqlConnection())
        {
            sqlConnection.ConnectionString =
                "Data Source=dell-n5110;Initial Catalog=Northwind; Integrated Security=true;";

            sqlConnection.Open();

            SqlCommand selectCommand = new SqlCommand("SELECT * FROM Products WHERE CategoryID = @CategoryID", sqlConnection);
            selectCommand.Parameters.Add("@CategoryID", SqlDbType.Int).Value = int.Parse(categoryDropList.SelectedValue);
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(selectCommand);

            sqlDataAdapter.Fill(productTable);
        }

        return productTable;
    }

    protected void categoryDropList_SelectedIndexChanged(object sender, EventArgs e)
    {
        BindGridView();
    }

    private void BindGridView()
    {
        //DataList1.DataSource = GetProducts();
        //DataList1.DataBind();

        DataTable productTable = GetProducts();
        productTable.DefaultView.RowFilter =
            "Discontinued = 0 AND ProductName LIKE '*" + productNameText.Text + "*'";
        GridView1.DataSource = productTable.DefaultView;
        GridView1.DataBind();

        DataView dataView = new DataView(productTable);
        dataView.RowFilter =
            "Discontinued = True AND ProductName LIKE '*" + productNameText.Text + "*'";
        GridView2.DataSource = dataView;
        GridView2.DataBind();
    }

    protected void DataList1_EditCommand(object source, DataListCommandEventArgs e)
    {
        DataList1.EditItemIndex = e.Item.ItemIndex; // gán mã dòng muốn sửa
        DataList1.DataSource = GetProducts();
        DataList1.DataBind();
    }

    protected void DataList1_UpdateCommand(object source, DataListCommandEventArgs e)
    {
        // Viết code update xuống db
        int productId = Convert.ToInt32(((Label)e.Item.FindControl("Label2")).Text);
        string productName = ((TextBox)e.Item.FindControl("TextBox1")).Text;


        // Dùng ADO.Net Update xuống
        using (SqlConnection sqlConnection = new SqlConnection())
        {
            sqlConnection.ConnectionString =
                "Data Source=dell-n5110;Initial Catalog=Northwind; Integrated Security=true;";

            sqlConnection.Open();

            SqlCommand updateCommand = new SqlCommand(
                "UPDATE Products SET ProductName = @ProductName WHERE ProductId = @ProductId",
                sqlConnection);

            SqlParameter parameter = new SqlParameter("@ProductId", SqlDbType.Int);
            parameter.Value = productId;
            updateCommand.Parameters.Add(parameter);

            parameter = new SqlParameter("@ProductName", SqlDbType.VarChar);
            parameter.Value = productName;
            updateCommand.Parameters.Add(parameter);

            updateCommand.ExecuteNonQuery();
        }

        DataList1.EditItemIndex = -1; // không còn dòng nào ở trạng thái đang sửa
        DataList1.DataSource = GetProducts();
        DataList1.DataBind();
    }
    protected void DataList1_CancelCommand(object source, DataListCommandEventArgs e)
    {
        DataList1.EditItemIndex = -1; // không còn dòng nào ở trạng thái đang sửa
        DataList1.DataSource = GetProducts();
        DataList1.DataBind();
    }
    protected void DataList1_DeleteCommand(object source, DataListCommandEventArgs e)
    {

    }
    protected void GridView1_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        GridView1.PageIndex = e.NewPageIndex;
        GridView1.DataSource = GetProducts();
        GridView1.DataBind();
    }
    protected void GridView1_Sorting(object sender, GridViewSortEventArgs e)
    {
        DataTable products = GetProducts();
        DataView productView = new DataView(products);

        if (e.SortDirection == SortDirection.Ascending)
            productView.Sort = e.SortExpression + " " + "ASC";
        else if (e.SortDirection == SortDirection.Descending)
            productView.Sort = e.SortExpression + " " + "DESC";

        GridView1.DataSource = productView;
        GridView1.DataBind();
    }
    protected void searchButton_Click(object sender, EventArgs e)
    {
        BindGridView();
    }
}